package com.dhcc.DRGsHosp.application.impl;

import java.util.List;
import java.util.Map;
import javax.inject.Named;
import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication;
import com.dhcc.DRGsHosp.infra.util.Arith;
/**
 * 
 * ClassName: AnalysisDRGsReportApplicationImpl  
 * Description ：简版新版分析报告导出-POI技术   
 * Reason: TODO ADD REASON(可选).   
 * date: 2017年11月7日 下午4:37:30  
 * @author ShaozhiYuan
 * @version V1.0 DHC
 */
@Named
@Transactional 
public class AnalysisDRGsReportApplicationImpl implements AnalysisDRGsReportApplication{
    @Autowired  
    private JdbcTemplateObj jdbcTemplateObj;
    
    private EntityRepository repository;
    
	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
    
	/**
	 * 
	 * @Description:获取当前系统中可用标杆  
	 * @return
	 */
	@Override
	public  List<Map<String,Object>> getARBenchmarkYear() {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT DISTINCT(a.AS_YEAR) AS_YEAR FROM drgs.t_annualstd a ORDER BY a.AS_YEAR DESC \n ");	
		 List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * @Description:获取系统中的分组器版本号
	 * @author shaozhiyuan 
	 * @date 2018年5月2日  
	 * @version V3.1.0
	 */
	@Override
	public  List<Map<String,Object>> getARDrgVersion() {
		StringBuilder sql = new StringBuilder();
		sql.append(" select DISTINCT T.VER_DESC DECC, t.ver_code CODE from drgs.T_DRGVERSION t\n ");	
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**  
	 * @Description: 根据分组方案获取分组器版本号
	 * @author shaozhiyuan 
	 * @date 2018年10月11日  
	 * @version V3.1.1
	 */  
	@Override
	public String getDRGversionCode(String groupScheme) {
		String drgsVersion="";
		String sql=" SELECT distinct p.ver_code from  drgs.t_patientdrg p where p.ver_region= '"+groupScheme+"' ";
		List<String> list = getRepository().createSqlQuery(sql).list();
		if(list.size()>0){
			drgsVersion=list.get(0).toString();
		}
		return drgsVersion;
	}
	/**
	 *获取当前系统库中所有的临床专业
	 */
	@Override
	public List<Map<String, Object>> getARBenchSpecCode(String drgVersion) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select  distinct cli.spec_desc DECC, cli.spec_code CODE   \n ");	
		sql.append(" from  DRGS.T_DEPARTMENTDRG t left join drgs.t_clispec cli   on  CLI.SPEC_CODE = t.parspec_code \n ");
		sql.append(" where t.VERSION='"+drgVersion+"' \n ");	
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 看日期区间内是否有数据  
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARDataIsNull(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public int findARDataIsNull(String dataFrom, String dataTo, String groupScheme, String hospCode) {
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT COUNT(p.PARID) FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  RIGHT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dataFrom,dataTo,"mr" )+" \n");
		sql.append(" and p.ver_region='"+groupScheme+"'\n");
		return jdbcTemplateObj.queryForObject(sql.toString(), Integer.class);
	}
	/**
	 * 
	 * TODO 获取新增病组
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARNewDrgs(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String,Object>> findARNewDrgs(String dateFrom, String dateTo, String stdId, String groupScheme, String hospCode) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT  tp.PARDRG_CODE PARDRG_CODE ,COUNT(tp.PARDRG_CODE) PARDRG_CNT FROM  \n");
		sql.append("(select mr.PMAININDEX ID from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=mr.PMAININDEX WHERE instr('"+hospCode+"',BA.p00010001)>0 and mr.P00320000 <61  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr" )+" ) tmr \n");
		sql.append(" JOIN (select p.PARID,p.PARDRG_CODE from drgs.t_patientdrg p where p.ver_region='"+groupScheme+"' and  p.PARDRG_CODE is NOT NULL and  NOT EXISTS \n");
		sql.append("(SELECT AS_DRG_CODE FROM drgs.t_annualstd ad WHERE p.PARDRG_CODE=ad.as_drg_code  AND ad.as_weight is not null and ad.AS_ID='"+stdId+"' ))tp  \n");
		sql.append(" ON tmr.ID=tp.PARID  \n");
		sql.append(" GROUP BY tp.PARDRG_CODE order by tp.PARDRG_CODE  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString());
		return list;
	}
	/**
	 * 
	 * TODO 校验数据完整性
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARCheckedData(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public  List<Map<String,Object>>  findARCheckedData(String dateFrom, String dateTo, String stdId, String groupScheme, String hospCode) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0301 is NOT null AND op.POTYPE='M' THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) FIRST_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0101 is NOT null AND op.POTYPE='M'  THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) TWO_JUDGE_VALUE , \n");
		sql.append(" decode(COUNT(op.PO0101),0,0,SUM(CASE WHEN op.PO0301 is not null  THEN 1 else 0 end)/COUNT(op.PO0101)) THREE_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PDTYPE is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end)) FOUR_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PD0101 is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end)) FIVE_JUDGE_VALUE\n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.mr_operation op on mr.PMAININDEX=op.PMAININDEX \n");
		sql.append(" LEFT  JOIN  drgs.MR_DIAGNOSE  di on mr.PMAININDEX=di.PMAININDEX\n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+"  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 查看该时间段内的科室总数
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARDepCount()
	 */
	@Override
	public int findARDepCount() {
		StringBuilder sql = new StringBuilder();	
		sql.append(" SELECT COUNT(1) FROM drgs.t_department \n");	
		return jdbcTemplateObj.queryForObject(sql.toString(), Integer.class);
	}
	/**
	 * 
	 * TODO 获取医院基本信息
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARSimpHospBaseQuota()
	 */
	@Override
	public List<Map<String, Object>> findARSimpHospBaseQuota(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" select Y.allcnt ALLCNT ,X.ingroup INGROUP,X.over60 OVERSIXTY,ROUND(decode(Y.allcnt-X.over60,0,0,X.ingroup*100/(Y.allcnt-X.over60)),2) GROUPRATIO ,X.notingroup NOTINGROUP \n");
		sql.append(" FROM ( select SUM(CASE WHEN mr.P00320000 > 60 THEN 1 else 0 end  ) over60, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is not NULL and p.ver_region='"+groupScheme+"' THEN 1 else 0 END ) ingroup, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is  NULL and p.ver_region='"+groupScheme+"'  THEN 1 else 0 END ) notingroup \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX left join drgs.t_patientdrg p on mr.PMAININDEX= p.PARID \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" ) X, \n");
		sql.append(" ( SELECT COUNT(mr.PMAININDEX) allcnt from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" ) Y \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 获取医院基本信息 表1
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findSimpHospBaseTableTwo(java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findSimpHospBaseTableOne(Map<String, Object> map) {
		  
		String reportDateFrom = map == null?"":map.get("dateFrom").toString();
		String reportDateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT a.allcnt ALLCNTTWO,ROUND(decode(a.allcnt,0,0,a.COSTAMOUNT/a.allcnt),2) hospavgfee,ROUND(decode(a.allcnt,0,0,a.dayscnt/a.allcnt),2) hospavgdays, \n");
		sql.append(" ROUND(decode(a.COSTAMOUNT,0,0,a.DRUGCOST*100/a.COSTAMOUNT),2) hospdrugratio, ROUND(decode(a.allcnt,0,0,a.CASEDEAD*100/a.allcnt),2) hospdeadratio FROM ( \n");
		sql.append(" SELECT COUNT(mr.PMAININDEX) allcnt ,SUM(mr.P00320000) dayscnt ,sum(mr.P00800000) COSTAMOUNT,\n");
		sql.append(" sum(exp.DRUG_COST) DRUGCOST ,SUM(CASE WHEN mr.P00620001 = 5 THEN 1 ELSE 0 END) CASEDEAD \n");
		sql.append(" FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  left join drgs.MR_EXPENSE_SUM exp on mr.PMAININDEX=exp.PMAININDEX where  \n");
		sql.append(" instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(reportDateFrom,reportDateTo,"mr")+")  a\n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 表格2 获取cmi，时间消耗指数，费用消耗指数，drg组数,总权重
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findSimpHospBaseTableTwo(java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findSimpHospBaseTableTwo(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append("  SELECT COUNT(DISTINCT(p.PARDRG_CODE)) hospdrgcnt,ROUND(decode(COUNT(p.PARID),0,0,SUM(a.AS_WEIGHT)/COUNT(p.PARID)),2) hospcmi ,SUM(a.AS_WEIGHT) hosptotalweight,\n");
		sql.append("  COUNT(p.PARID) hospingroupcnt ,round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_FEE,0,0,mr.P00800000/a.AS_AVG_FEE))/COUNT(p.parid)),2)  hospeffee, \n");
		sql.append("  round(decode(COUNT(p.parid),0,0,sum(decode(a.AS_AVG_DAYS,0,0,mr.P00320000/a.AS_AVG_DAYS))/COUNT(p.parid)),2) hosptimefee, \n");
		sql.append("  round(sum(case when mr.P00620001='5' and a.AS_RISK_LEVEL='1' then 1 else 0 END)/sum(case WHEN a.AS_RISK_LEVEL='1' then 1 else 0 END)*100,2) deadriskAratio \n");
		sql.append("  FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN  drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID LEFT JOIN   drgs.t_annualstd a ON p.PARDRG_CODE=a.AS_DRG_CODE \n");
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not null AND a.AS_ID='"+stdId+"' and mr.P00320000 <61  and a.as_weight is not null  \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");	
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 获取手术病例
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findHospCaseOperationCount(java.util.Map)
	 */
	@Override
	public String findHospCaseOperationCount(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String sqlResult = "SELECT COUNT(A.PMAININDEX)  FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX= B.PMAININDEX   JOIN DRGS.MR_OPERATION A ON A.PMAININDEX = B.PMAININDEX WHERE instr('"+hospCode+"',BA.p00010001)>0 and A.PO1400 ='1'  and  A.POTYPE='M' "+Arith.mrAROutDateSql(dateFrom,dateTo,"B");
		String caseOperationCount = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return caseOperationCount;
	}
	/**
	 * 获取手术版本
	 */
	@Override
	public String findHospCaseOperationVersion(Map<String, Object> map) {
		String sqlResult = " select t.cfg_value from DRGS.T_CONFIG t where t.CFG_CODE='OPER_VERSION' ";
		String caseOperationCount = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return caseOperationCount;
	}
	/**
	 * 获取该手术版本信息下的字典数
	 */
	@Override
	public String findHospCaseOperVersionCnt(Map<String, Object> map) {
		String operVersion = map == null?"":map.get("operVersion").toString().trim();
		String sqlResult = " select count(1) from drgs.T_ICD_BJ_OPER t where t.oicd_version='"+operVersion+"' ";
		String caseOperationCount = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		return caseOperationCount;
	}
	 /**
	  *更新手术操作字段
	  */
    @Override
	public void updateHospCaseOper(Map<String, Object> paramMap) {
    	String operVersion = paramMap == null?"":paramMap.get("operVersion").toString().trim();
    	String sql = "";
    	sql+=" UPDATE drgs.MR_OPERATION op set op.PO1400=( select io.is_oper  from drgs.T_ICD_BJ_OPER io ";
    	sql+=" where io.oicd_code=op.PO0101 and  io.oicd_version='"+operVersion+"' ) where op.potype='M' ";
    	getRepository().createSqlQuery(sql).executeUpdate();
	}
	/**
	 * 
	 * TODO 获取手术级别
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findOperCaseByLvel(java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> findOperCaseByLvel(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT OP.PO0301 opercaselevel,COUNT(OP.PMAININDEX) opercasecnt \n");
		sql.append(" FROM drgs.MR_IPINFO mr , DRGS.MR_BASEINFO  BA ,drgs.MR_OPERATION OP  \n");
	    sql.append(" WHERE BA.PMAININDEX=MR.PMAININDEX and instr('"+hospCode+"', BA.p00010001)>0 and op.PO1400 ='1' and mr.PMAININDEX=OP.PMAININDEX and OP.POTYPE='M' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY OP.PO0301 ORDER BY COUNT(OP.PMAININDEX) DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 主要手术病例数降序排列前二十位的手术
	 */
	@Override
	public List<Map<String, Object>> findtopTwentyOper(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT * FROM (SELECT b.PO0100 AS operName, COUNT(B.PMAININDEX) AS operCnt FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX \n ");	
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n ");	
		sql.append(" GROUP BY b.PO0100 ORDER BY COUNT(B.PMAININDEX) DESC) WHERE ROWNUM<21  \n ");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 各级手术的总数
	 */
	@Override
	public List<Map<String, Object>> findmainOperLevelInfo(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT b.PO0301 EVELEVEL, COUNT(B.PMAININDEX) EVECNT FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX \n ");	
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n ");	
		sql.append("  GROUP BY b.PO0301 ORDER BY b.PO0301 ASC \n ");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 各级手术出现次数最多的前五位主要手术名称
	 */
	@Override
	public List<Map<String, Object>> findtopCaseByLevel(Map<String, Object> map, int level) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		if("4".equals(level+"")||"3".equals(level+"")||"2".equals(level+"")||"1".equals(level+"")){
			sql.append("  SELECT  b.PO0101 CODE, b.PO0100 DECC, COUNT(B.PMAININDEX) CNT FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX  \n");
			sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' AND B.PO0301='"+level+"' AND \n");
			sql.append("  1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"   \n");
			sql.append("  GROUP BY b.PO0101,B.PO0100 ORDER BY CNT DESC \n");
		}else{
			sql.append("  SELECT  b.PO0101 CODE, b.PO0100 DECC, COUNT(B.PMAININDEX) CNT FROM drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX  \n");
			sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' AND B.PO0301 not in ('1','2','3','4') AND \n");
			sql.append("  1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  \n");
			sql.append("  GROUP BY b.PO0101,B.PO0100 ORDER BY CNT DESC \n");
		}
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 各临床专业主要手术分布
	 */
	@Override
	public List<Map<String, Object>> findParLevelInfoList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String isRefine = map == null?"":map.get("isRefine").toString(); //是否精细化 1是精细
		
		StringBuilder sql = new StringBuilder();
		sql.append("  select CLI.SPEC_DESC CODE ,sum(case when B.PO0301='1' then 1 else 0 end) ACNT, ");
		sql.append("  sum(case when B.PO0301='2' then 1 else 0 end) BCNT, ");
		sql.append("  sum(case when B.PO0301='3' then 1 else 0 end) CCNT, ");
		sql.append("  sum(case when B.PO0301='4' then 1 else 0 end) DCNT, ");
		sql.append("  decode(sum(case when B.PO0301='1' or B.PO0301='2' or  B.PO0301='3'or B.PO0301='4' then 1 else 0 end),0,0,round(sum(case when B.PO0301='3'or B.PO0301='4' then 1 else 0 end)/sum(case when B.PO0301='1' or B.PO0301='2' or  B.PO0301='3'or B.PO0301='4' then 1 else 0 end)*100 ,2)) CDRIO ");
		sql.append("  from  drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX LEFT JOIN drgs.MR_OPERATION b on a.PMAININDEX=b.PMAININDEX  ");
		sql.append("  left join drgs.t_patientdrg pg on b.pmainindex=pg.parid ");
		sql.append("  left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' \n");
		sql.append("  GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=a.P00300001 \n");
		sql.append("  LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code \n");
		sql.append("  where CLI.SPEC_DESC is not null and pg.ver_region='"+groupScheme+"' and instr('"+hospCode+"',BA.p00010001)>0 and b.PO1400='1' and b.POTYPE='M' AND  \n");
		sql.append("  1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  \n");//cb
		if("1".equals(isRefine)){
			sql.append(" "+Arith.pdInDpDRGSql(drgVersion,"all","pg")+" \n");
		}
		sql.append("  GROUP BY CLI.SPEC_DESC  ");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 十八个重点手术
	 */
	@Override
	public List<Map<String, Object>> findkeyEighteenOper(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();
		sql.append(" select operName,opercnt from (select operName,count(*) as opercnt from  \n ");	
		sql.append(" (select case when (instr(po0101,'81.5')>0) then '髋、膝关节置换术' \n ");	
		sql.append(" when (instr('03.0,03.1,03.2,03.4,03.5,03.6,03.7', substr(po0101,1,4))>0) then '脊髓、椎管手术'  \n ");
		sql.append(" when (instr('52.5,52.6,52.7', substr(po0101,1,4))>0) then '胰腺切除术'  \n ");	
		sql.append(" when (instr(po0101,'42.4')>0) then '食管切除术' \n ");	
		sql.append(" when (instr(po0101,'51.23')>0) then '腹腔镜下胆囊切除术'  \n ");
		sql.append(" when (instr(po0101,'36.1')>0) then '冠状动脉旁路移植术（CABG）'  \n ");	
		sql.append(" when (instr('36.06,36.07', substr(po0101,1,5))>0) then '经皮冠状动脉介入治疗（PCI）' \n ");	
		sql.append(" when (instr('01.24,01.39', substr(po0101,1,5))>0 or instr(po0101,'01.5')>0) then '颅脑手术' \n ");
		sql.append(" when (instr('68.4,68.5,68.6,68.7', substr(po0101,1,4))>0) then '子宫切除术' \n ");	
		sql.append(" when (instr('74.0,74.1,74.2,74.4', substr(po0101,1,4))>0 or instr(po0101,'74.99')>0) then '剖宫产' \n ");	
		sql.append(" when (instr(po0101,'85.4')>0) then '乳腺手术' \n ");
		sql.append(" when (instr('32.4,32.5', substr(po0101,1,4))>0) then '肺切除术' \n ");	
		sql.append(" when (instr('43.5,43.6,43.7,43.8,43.9', substr(po0101,1,4))>0) then '胃切除术' \n ");	
		sql.append(" when (instr('48.4,48.5,48.6', substr(po0101,1,4))>0) then '直肠切除术' \n ");	
		sql.append(" when (instr('55.4,55.5,55.6,60.3,60.4,60.5', substr(po0101,1,4))>0) then '肾与前列腺相关手术' \n ");	
		sql.append(" when (instr('39.71,39.72,39.73,39.74', substr(po0101,1,5))>0) then '血管内修补术' \n ");	
		sql.append(" else null end operName  from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  \n ");	
		sql.append(" left join drgs.mr_operation oper on a.pmainindex=oper.pmainindex \n ");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and oper.potype='M' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" ) where opername is not null group by operName) \n ");	
		sql.append(" union (select operName, count(*) as opercnt from(select \n ");
		sql.append(" case when (instr(pd0101,'C')>0 and (instr(po0100,'全切除术')>0 or instr(po0100,'部分切除术')>0)) then '恶性肿瘤根治术' \n ");
		sql.append(" else null end operName  from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX left join drgs.mr_operation oper on a.pmainindex=oper.pmainindex \n ");	
		sql.append(" left join drgs.mr_diagnose dia on a.pmainindex=dia.pmainindex where instr('"+hospCode+"',BA.p00010001)>0 and oper.potype='M' and dia.pdtype='M' \n ");	
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" )where opername is not null group by operName) \n ");
		sql.append(" union (select operName, count(*) as opercnt from(select  \n ");
		sql.append(" case when (instr(pd0101,'Z37')>0 and (instr('73.0,73.1,73.2,73.4,73.5,73.6,73.7,73.8,73.9', substr(po0101,1,4))>0 or \n ");	
		sql.append(" instr(po0101,'72')>0)) then '阴道分娩' else null end operName  from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  \n ");
		sql.append(" left join drgs.mr_operation oper on a.pmainindex=oper.pmainindex left join drgs.mr_diagnose dia on a.pmainindex=dia.pmainindex \n ");	
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and oper.potype='M' "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" ) where opername is not null group by operName) order by opercnt desc \n ");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 获取DRG病组,内科组，外科组，操作组
	 */
	@Override
	public List<Map<String, Object>> findHospDRGsInfo(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuffer sql= new StringBuffer();
		sql.append("	SELECT  (AZ.totaldrg) ALLDRGS,AX.WAIKE WKDRGS,AX.NEIKE NKDRGS,AX.CAOZUO CZDRGS ,AY.WAIKEY WKCNT,AY.NEIKEY NKCNT,AY.CAOZUOY CZCNT FROM (  \n ");
		sql.append("	SELECT  SUM(case WHEN SUBSTR(X.drgcode,2,1)='A' OR SUBSTR(X.drgcode,2,1)='B' OR SUBSTR(X.drgcode,2,1)='C' \n ");
		sql.append("	 OR SUBSTR(X.drgcode,2,1)='D' OR SUBSTR(X.drgcode,2,1)='E' OR SUBSTR(X.drgcode,2,1)='F' OR SUBSTR(X.drgcode,2,1)='G' \n ");
		sql.append("	 OR SUBSTR(X.drgcode,2,1)='H' OR SUBSTR(X.drgcode,2,1)='J'  THEN 1 ELSE 0 END) AS WAIKE,  \n ");
		sql.append("	SUM(case WHEN SUBSTR(X.drgcode,2,1)='K' OR SUBSTR(X.drgcode,2,1)='L' OR SUBSTR(X.drgcode,2,1)='M'   \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='N' OR SUBSTR(X.drgcode,2,1)='P' OR SUBSTR(X.drgcode,2,1)='Q'  THEN 1 ELSE 0 END) AS CAOZUO,  \n ");
		sql.append("	SUM(case WHEN SUBSTR(X.drgcode,2,1)='R' OR SUBSTR(X.drgcode,2,1)='S' OR SUBSTR(X.drgcode,2,1)='T' OR SUBSTR(X.drgcode,2,1)='U'  \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='V' OR SUBSTR(X.drgcode,2,1)='W' OR SUBSTR(X.drgcode,2,1)='X'  \n ");
		sql.append("	OR SUBSTR(X.drgcode,2,1)='Y' OR SUBSTR(X.drgcode,2,1)='Z'  THEN 1 ELSE 0 END) AS NEIKE  \n ");
		sql.append("	FROM (SELECT DISTINCT p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID \n ");
		sql.append("	 where instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.ver_region='"+groupScheme+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n ");
		sql.append("	)X)AX ,(SELECT SUM(case WHEN SUBSTR(Y.drgcode,2,1)='A' OR SUBSTR(Y.drgcode,2,1)='B' OR SUBSTR(Y.drgcode,2,1)='C' \n ");
		sql.append("	 OR SUBSTR(Y.drgcode,2,1)='D' OR SUBSTR(Y.drgcode,2,1)='E' OR SUBSTR(Y.drgcode,2,1)='F' OR SUBSTR(Y.drgcode,2,1)='G'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='H' OR SUBSTR(Y.drgcode,2,1)='J'  THEN 1 ELSE 0 END) AS WAIKEY, \n ");
		sql.append("	 SUM(case WHEN SUBSTR(Y.drgcode,2,1)='K' OR SUBSTR(Y.drgcode,2,1)='L' OR SUBSTR(Y.drgcode,2,1)='M'  \n ");
		sql.append("      OR SUBSTR(Y.drgcode,2,1)='N' OR SUBSTR(Y.drgcode,2,1)='P' OR SUBSTR(Y.drgcode,2,1)='Q'  THEN 1 ELSE 0 END) AS CAOZUOY, \n ");
		sql.append("	SUM(case WHEN SUBSTR(Y.drgcode,2,1)='R' OR SUBSTR(Y.drgcode,2,1)='S' OR SUBSTR(Y.drgcode,2,1)='T'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='U' OR SUBSTR(Y.drgcode,2,1)='V' OR SUBSTR(Y.drgcode,2,1)='W' OR SUBSTR(Y.drgcode,2,1)='X'  \n ");
		sql.append("	OR SUBSTR(Y.drgcode,2,1)='Y' OR SUBSTR(Y.drgcode,2,1)='Z'  THEN 1 ELSE 0 END) AS NEIKEY \n ");
		sql.append("	FROM (SELECT  p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n ");
		sql.append("	 where instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.ver_region='"+groupScheme+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" )Y) AY,  \n ");
		sql.append("    (SELECT count(DISTINCT p.PARDRG_CODE) as totaldrg from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n ");
		sql.append("	where instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.ver_region='"+groupScheme+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" )AZ  \n ");
		
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * DRG病组和病例复杂程度
	 */
	@Override
	public List<Map<String, Object>> findPersonCnt(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		StringBuilder sql = new StringBuilder();	
		sql.append(" SELECT AX.DRGsType1Num DRGCA,AX.DRGsType3Num DRGCB,AX.DRGsType5Num DRGCC,AY.CaseType1Num CASEA,AY.CaseType3Num CASEB,AY.CaseType5Num CASEC FROM ( \n");
		sql.append(" SELECT SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('1','2') THEN 1 ELSE 0 END) AS DRGsType1Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('3','4')  THEN 1 ELSE 0 END) AS DRGsType3Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(X.drgcode,4,1) in ('5','6')  THEN 1 ELSE 0 END) AS DRGsType5Num \n");
		sql.append(" FROM ( \n");
		sql.append("  SELECT DISTINCT p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID  \n");
		sql.append("  where instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.ver_region='"+groupScheme+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append("  )X \n");
		sql.append(" )AX ,\n");
		sql.append(" (SELECT SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('1','2')   THEN 1 ELSE 0 END) AS CaseType1Num, \n");
		sql.append("  SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('3','4')  THEN 1 ELSE 0 END) AS CaseType3Num, \n");
		sql.append(" SUM(case WHEN SUBSTR(Y.drgcode,4,1) in ('5','6')  THEN 1 ELSE 0 END) AS CaseType5Num \n");
		sql.append(" FROM ( \n");
		sql.append(" SELECT  p.PARDRG_CODE  as drgcode from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  LEFT JOIN drgs.t_patientdrg p on mr.PMAININDEX=p.PARID \n");
		sql.append("  where instr('"+hospCode+"',BA.p00010001)>0 and p.PARDRG_CODE is not null and p.ver_region='"+groupScheme+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+"\n");
		sql.append("  )Y) AY \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 
	 * TODO 不同权重（RW）区间病例数(全院)
	 */
	@Override
	public List<Map<String, Object>> RWDistribute(Map<String, Object> map, String type) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String specCode = map == null?"":map.get("specCode").toString().trim();
		StringBuffer sql= new StringBuffer();		
		sql.append("  select greatest (ONEPART, TWOPART,THREEPART,FOURPART,FIVEPART,SIXPART) max,ONEPART, TWOPART,THREEPART,FOURPART,FIVEPART,SIXPART from ( \n");
		sql.append("  SELECT SUM(CASE WHEN STD.AS_WEIGHT>0 AND AS_WEIGHT<=0.5 THEN 1 ELSE 0 END) AS ONEPART, \n");
		sql.append("  SUM(CASE WHEN STD.AS_WEIGHT>0.5 AND AS_WEIGHT<=0.8 THEN 1 ELSE 0 END) AS TWOPART, \n");
		sql.append("  SUM(CASE WHEN STD.AS_WEIGHT>0.8 AND AS_WEIGHT<=1.2 THEN 1 ELSE 0 END) AS THREEPART, \n");
		sql.append("  SUM(CASE WHEN STD.AS_WEIGHT>1.2 AND AS_WEIGHT<=2 THEN 1 ELSE 0 END) AS FOURPART, \n");
		sql.append("  SUM(CASE WHEN STD.AS_WEIGHT>2 AND AS_WEIGHT<=5 THEN 1 ELSE 0 END) AS FIVEPART, \n");
		sql.append("  SUM(CASE WHEN STD.AS_WEIGHT>5 THEN 1 ELSE 0 END) AS SIXPART FROM DRGS.MR_IPINFO A \n");
		sql.append("  LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX LEFT JOIN  drgs.t_patientdrg p ON a.PMAININDEX=p.PARID LEFT JOIN   drgs.t_annualstd std ON p.PARDRG_CODE=std.AS_DRG_CODE  \n");
		sql.append("  LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001 \n");
		sql.append("  left join drgs.t_clispec spec on spec.spec_code =dd.parspec_code \n");
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not null AND std.AS_ID='"+stdId+"' and a.P00320000 <61  and std.as_weight is not null  \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n");
		if("one".equals(type)){
			sql.append(" and spec.spec_code='"+specCode+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"p")+" \n");
		}
		sql.append(" ) \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	
	/**
	 * 
	 * TODO 专科建设DRGs组数与CMI、专科建设DRGs危重等级分布
	 */
	@Override
	public List<Map<String, Object>> findSpecDRGsCntAndCMI(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String isRefine = map == null?"":map.get("isRefine").toString(); //是否精细化 1是精细
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		StringBuffer sql= new StringBuffer();
		sql.append("  SELECT A.SPEC_DESC,A.TOTALWEIGHT,A.CMI,A.DRGSCNT,B.DRGS1CNT,B.DRGS3CNT,B.DRGS5CNT,B.DRGS7CNT,DRGS9CNT FROM ( \n");
		sql.append("  SELECT * FROM (SELECT SPEC.SPEC_DESC,ROUND(SUM(STD.AS_WEIGHT),2) TOTALWEIGHT, \n");
		sql.append("  ROUND(decode(COUNT(B.PARID),0,0,SUM(STD.AS_WEIGHT) / COUNT(B.PARID)), 2 ) CMI,COUNT(DISTINCT B.PARDRG_CODE) DRGSCNT FROM DRGS.MR_IPINFO A \n");
		sql.append("  LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX LEFT JOIN DRGS.T_PATIENTDRG B ON A.PMAININDEX=B.PARID AND B.ver_region='"+groupScheme+"' \n");
		sql.append("  LEFT JOIN DRGS.T_ANNUALSTD STD ON STD.AS_DRG_CODE=B.PARDRG_CODE AND std.AS_ID='"+stdId+"' \n");
		sql.append("  LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.VERSION='"+drgVersion+"' \n");
		sql.append("  GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001 \n");
		sql.append("  LEFT JOIN DRGS.T_CLISPEC SPEC ON SPEC.SPEC_CODE=DD.PARSPEC_CODE WHERE instr('"+hospCode+"',BA.p00010001)>0 and SPEC.SPEC_CODE IS NOT NULL AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n");
		if("1".equals(isRefine)){
			sql.append(" "+Arith.pdInDpDRGSql(drgVersion,"all","B")+" \n");
			}
		sql.append("  GROUP BY SPEC.SPEC_DESC ORDER BY TOTALWEIGHT DESC)WHERE ROWNUM<11)A LEFT JOIN \n");
		sql.append("  (select SPEC_DESC, sum(case when substr(pardrg_code,4,1)='1' or substr(pardrg_code,4,1)='2' then 1 else 0 end) DRGS1CNT, \n");
		sql.append("  sum(case when substr(pardrg_code,4,1)='3' or substr(pardrg_code,4,1)='4' then 1 else 0 end) DRGS3CNT,sum(case when substr(pardrg_code,4,1)='5' or substr(pardrg_code,4,1)='6' then 1 else 0 end) DRGS5CNT, \n");
		sql.append("  sum(case when substr(pardrg_code,4,1)='7' then 1 else 0 end) DRGS7CNT, \n");
		sql.append("  sum(case when substr(pardrg_code,4,1)='8' or substr(pardrg_code,4,1)='9' or substr(pardrg_code,4,1)='0' then 1 else 0 end) DRGS9CNT \n");
		sql.append("  from(SELECT distinct b.pardrg_code,SPEC.SPEC_DESC FROM DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX \n");
		sql.append("  LEFT JOIN DRGS.T_PATIENTDRG B ON A.PMAININDEX=B.PARID AND B.ver_region='"+groupScheme+"' \n");
		sql.append("  LEFT JOIN DRGS.T_ANNUALSTD STD ON STD.AS_DRG_CODE=B.PARDRG_CODE AND std.AS_ID='"+stdId+"' \n");
		sql.append("  LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.VERSION='"+drgVersion+"' \n");
		sql.append("  GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001 \n");
		sql.append("  LEFT JOIN DRGS.T_CLISPEC SPEC ON SPEC.SPEC_CODE=DD.PARSPEC_CODE WHERE instr('"+hospCode+"',BA.p00010001)>0 and SPEC.SPEC_CODE IS NOT NULL AND B.PARDRG_CODE IS NOT NULL \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" \n");
		if("1".equals(isRefine)){
			sql.append(" "+Arith.pdInDpDRGSql(drgVersion,"all","B")+" \n");
			}
		sql.append("  GROUP BY b.pardrg_code,SPEC.SPEC_DESC )group by spec_desc)B on A.SPEC_DESC=B.SPEC_DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 获取临床专业时间费用消耗指数
	 */
	@Override
	public List<Map<String, Object>> findEfficiencyDepInfoList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String isRefine = map == null?"":map.get("isRefine").toString(); //是否精细化 1是精细
		
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT CLI.SPEC_CODE CODE, CLI.SPEC_DESC DECC ,round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_FEE,0,0,E.P00800000/G.AS_AVG_FEE))/COUNT(G.parid)),2) FEE, \n");
		sql.append(" round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_DAYS,0,0,E.P00320000/G.AS_AVG_DAYS))/COUNT(G.parid)),2) TIM \n");
		sql.append(" FROM  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=E.PMAININDEX  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid \n");
		sql.append(" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' \n");
		sql.append(" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  \n");
		sql.append(" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code RIGHT JOIN ( select distinct A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS  FROM DRGS.T_PATIENTDRG A left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE  \n");
		sql.append(" left join DRGS.MR_IPINFO B on A.PARID=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX  where instr('"+hospCode+"',BA.p00010001)>0 and T.AS_ID='"+stdId+"' AND A.PARDRG_CODE IS NOT NULL and  \n");
		sql.append(" B.P00320000 <61 "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" AND A.ver_region='"+groupScheme+"'   \n");
		sql.append(" ) G  ON E.PMAININDEX = G.PARID where CLI.SPEC_CODE is not null and instr('"+hospCode+"',BA.p00010001)>0  \n");
		if("1".equals(isRefine)){
			sql.append(" "+Arith.pdInDpDRGSql(drgVersion,"all","pg")+" \n");
			}
		sql.append(" GROUP BY CLI.SPEC_CODE, CLI.SPEC_DESC  ORDER BY round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_FEE,0,0,E.P00800000/G.AS_AVG_FEE))/COUNT(G.parid)),2) DESC, round(decode(COUNT(G.parid),0,0,sum(decode(G.AS_AVG_DAYS,0,0,E.P00320000/G.AS_AVG_DAYS))/COUNT(G.parid)),2)  DESC\n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *表格 12  全院低风险组死亡病例病组分布表
	 */
	@Override
	public List<Map<String, Object>> findQualityDepTableByRiskAList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT DG.CODE CODE,DG.DECC DECC, DG.dep DEP, DG.incnt INC, DG.deadcnt DCN,DG.ratio RAT FROM (  \n");
		sql.append(" SELECT ds.DRG_CODE CODE, ds.DRG_DESC DECC ,a.P00300000 dep ,sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='1' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='1'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID LEFT JOIN DRGS.T_DRGS ds ON b.PARDRG_CODE= ds.DRG_CODE and ds.ver_code='"+drgVersion+"' \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and a.P00320000 <61 and b.PARDRG_CODE is not NULL  \n");
		sql.append(" and c.AS_ID='"+stdId+"' AND 1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"   \n");
		sql.append(" GROUP BY ds.DRG_CODE , ds.DRG_DESC  ,a.P00300000  ORDER BY deadcnt DESC   \n");
		sql.append(" )DG WHERE DG.incnt <>0 AND DG.ratio >0  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *表格 12  全院中低风险组死亡病例病组分布表
	 */
	@Override
	public List<Map<String, Object>> findQualityDepTableByRiskBList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT DG.CODE CODE,DG.DECC DECC, DG.dep DEP, DG.incnt INC, DG.deadcnt DCN,DG.ratio RAT FROM (  \n");
		sql.append(" SELECT ds.DRG_CODE CODE, ds.DRG_DESC DECC ,a.P00300000 dep ,sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)  incnt,  \n");
		sql.append(" sum(case WHEN c.AS_RISK_LEVEL='2' AND a.P00620001='5' then 1 else 0 end)  deadcnt, \n");
		sql.append(" ROUND(decode(sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN c.AS_RISK_LEVEL='2'AND a.P00620001='5' then 1 else 0 end)/sum(case WHEN c.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as ratio \n");
		sql.append(" from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=a.PMAININDEX  LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID LEFT JOIN DRGS.T_DRGS ds ON b.PARDRG_CODE= ds.DRG_CODE and ds.ver_code='"+drgVersion+"' \n");
		sql.append(" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and a.P00320000 <61 and b.PARDRG_CODE is not NULL  \n");
		sql.append(" and c.AS_ID='"+stdId+"' AND 1=1 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  \n");
		sql.append(" GROUP BY ds.DRG_CODE , ds.DRG_DESC  ,a.P00300000  ORDER BY deadcnt DESC   \n");
		sql.append(" )DG WHERE DG.incnt <>0 AND DG.ratio >0  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 全院出院病例数最多的二十个病种
	 */
	@Override
	public List<Map<String, Object>> findDepOutADRGList(Map<String, Object> map) {
		 String dateFrom = map == null?"":map.get("dateFrom").toString();
		 String dateTo = map == null?"":map.get("dateTo").toString();
		 String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		 String drgVersion = map == null?"":map.get("drgVersion").toString();
		 String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		 String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		 String sql="";
		 sql+="";
		 sql+="	SELECT X.DECC DECC, X.CNT CNT, X.CMI CMI, X.DAYS DAYS, X.AFEE AFEE FROM ";
		 sql+=" (select  ag.adrg_desc DECC ,count(distinct b.parid) CNT, ROUND(decode(COUNT(distinct b.PARID),0,0,SUM(c.AS_WEIGHT)/COUNT(distinct b.PARID)),2) CMI,round(avg(a.P00320000),2) DAYS,round(avg(a.P00800000),2) AFEE ";
		 sql+=" from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=a.PMAININDEX LEFT JOIN drgs.t_patientdrg b  ON a.PMAININDEX=b.PARID ";
		 sql+=" LEFT JOIN DRGS.T_DRGS ds ON b.PARDRG_CODE= ds.DRG_CODE and ds.ver_code='"+drgVersion+"' ";
		 sql+=" LEFT JOIN DRGS.T_ADRG ag on ag.adrg_code=ds.adrg_code  and ag.ver_code='"+drgVersion+"' ";
		 sql+=" LEFT JOIN drgs.t_annualstd c on b.PARDRG_CODE=c.AS_DRG_CODE ";
		 sql+=" where instr('"+hospCode+"',BA.p00010001)>0 and a.P00320000 <61 and b.PARDRG_CODE is not NULL and B.ver_region='"+groupScheme+"' ";
		 sql+=" and c.AS_ID='"+stdId+"' AND 1=1  "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" ";   
		 sql+=" group by ag.adrg_desc ORDER BY count(distinct b.parid) DESC ) X WHERE rownum<=20 ";
		 List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		 return list;
	}
	/**
	 * 表格 15  全院重点病种医疗服务能力
	 */
	@Override
	public List<Map<String, Object>> findDepMainADRGList(Map<String, Object> map) {
		 String dateFrom = map == null?"":map.get("dateFrom").toString();
		 String dateTo = map == null?"":map.get("dateTo").toString();
		 String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		 String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		 String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		 String sql="";
		 sql+="";
		 sql+=" select diaName  DECC ,count(*)/4 CNT,round(count(*)/b.totalcasecnt*100,2) RAT, ";
		 sql+="  round(sum(as_weight)/sum(inCaseCnt),2) CMI,round(avg(P00320000),2) DAYS,round(avg(P00800000),2) AFEE from ";
		 sql+=" (select case when p.pardrg_code is not null then 1 else 0 end inCaseCnt,std.as_weight,a.P00320000,a.P00800000, ";
		 sql+=" case when (instr(pd0101,'I20')>0 or instr(pd0101,'I21')>0) then '急性心肌梗塞' ";
		 sql+=" when (instr(pd0101,'I50.0')>0) then '充血性心力衰竭' ";
		 sql+=" when (instr('I60,I61,I62,I63', substr(pd0101,1,3))>0) then '脑出血和脑梗塞'  ";
		 sql+=" when (instr(pd0101,'S06')>0) then '创伤性颅脑损伤' ";
		 sql+=" when (instr('K25.0,K25.1,K25.2,K25.4,K25.5,K25.6,K26.0,K26.1,K26.2,K26.4,K26.5,K26.6,K27.0,K27.1,K27.2,K27.4,K27.5,K27.6, ";
		 sql+=" K28.0,K28.1,K28.2,K28.4,K28.5,K28.6,K29.0,K92.2', substr(pd0101,1,5))>0) then '消化道出血（无并发症）' ";
		 sql+=" when (instr('T00,T01,T02,T03,T04,T05,T06,T07', substr(pd0101,1,3))>0) then '累及身体多个部位的损伤'  ";
		 sql+=" when (instr('J12,J13,J14,J15,J16,J18', substr(pd0101,1,3))>0 or instr('J10.0,J11.0', substr(pd0101,1,5))>0) then '细菌性肺炎（成人、无并发症）' ";
		 sql+=" when (instr(pd0101,'J44')>0) then '慢性阻塞性肺疾病' ";
		 sql+=" when (instr('E10,E11,E12,E13,E14', substr(pd0101,1,3))>0) then '糖尿病伴短期并发症与长期并发症' ";
		 sql+=" when (instr(pd0101,'E04')>0) then '结节性甲状腺肿'  ";
		 sql+=" when (instr('K35.0,K35.1', substr(pd0101,1,5))>0) then '急性阑尾炎伴弥漫性腹膜炎及脓肿' ";
		 sql+=" when (instr(pd0101,'N40')>0) then '前列腺增生'  ";
		 sql+=" when (instr('N17,N18,N19', substr(pd0101,1,3))>0) then '肾功能衰竭' ";
		 sql+=" when (instr('A40,A41', substr(pd0101,1,3))>0) then '败血症（成人）' ";
		 sql+=" when (instr('I10,I11,I12,I13,I14,I15', substr(pd0101,1,3))>0) then '高血压病（成人）'  ";
		 sql+=" when (instr(pd0101,'K85')>0) then '急性胰腺炎' ";
		 sql+=" when (instr(pd0101,'Z51.101')>0) then '恶性肿瘤术后化疗' ";
		 sql+=" when (instr('Z51.201,Z51.103', substr(pd0101,1,5))>0) then '恶性肿瘤维持性化学治疗' ";
		 sql+=" else null end diaName  from drgs.MR_IPINFO a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX ";
		 sql+=" left join drgs.mr_diagnose dia on a.pmainindex=dia.pmainindex ";
		 sql+="	LEFT JOIN  drgs.t_patientdrg p ON a.PMAININDEX=p.PARID and p.ver_region='"+groupScheme+"' ";
		 sql+=" LEFT JOIN   drgs.t_annualstd std ON p.PARDRG_CODE=std.AS_DRG_CODE AND std.AS_ID='"+stdId+"'  ";
		 sql+=" where instr('"+hospCode+"',BA.p00010001)>0 and dia.pdtype='M' ";
		 sql+=" AND 1=1  "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" ";
		 sql+=" and (instr(pd0101,'I20')>0 or instr(pd0101,'I21')>0 ";
		 sql+=" or instr(pd0101,'I50.0')>0 ";
		 sql+=" or instr('I60,I61,I62,I63', substr(pd0101,1,3))>0 ";
		 sql+=" or instr(pd0101,'S06')>0 ";
		 sql+=" or instr('K25.0,K25.1,K25.2,K25.4,K25.5,K25.6,K26.0,K26.1,K26.2,K26.4,K26.5,K26.6,K27.0,K27.1,K27.2,K27.4,K27.5,K27.6, ";
		 sql+=" K28.0,K28.1,K28.2,K28.4,K28.5,K28.6,K29.0,K92.2', substr(pd0101,1,5))>0 ";
		 sql+=" or instr('T00,T01,T02,T03,T04,T05,T06,T07', substr(pd0101,1,3))>0 ";
		 sql+=" or instr('J12,J13,J14,J15,J16,J18', substr(pd0101,1,3))>0 or instr('J10.0,J11.0', substr(pd0101,1,5))>0 ";
		 sql+=" or instr(pd0101,'J44')>0 ";
		 sql+=" or instr('E10,E11,E12,E13,E14', substr(pd0101,1,3))>0 ";
		 sql+=" OR instr(pd0101,'E04')>0 ";
		 sql+=" OR instr('K35.0,K35.1', substr(pd0101,1,5))>0 ";
		 sql+=" OR instr(pd0101,'N40')>0 ";
		 sql+=" or instr('N17,N18,N19', substr(pd0101,1,3))>0 ";
		 sql+=" OR instr('A40,A41', substr(pd0101,1,3))>0 ";
		 sql+=" OR instr('I10,I11,I12,I13,I14,I15', substr(pd0101,1,3))>0 ";
		 sql+=" OR instr(pd0101,'K85')>0 ";
		 sql+=" OR instr(pd0101,'Z51.101')>0 ";
		 sql+=" OR instr('Z51.201,Z51.103', substr(pd0101,1,5))>0 ";
		 sql+=" ) )alldia left join (select count(*) totalcasecnt from drgs.mr_ipinfo a LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=a.PMAININDEX  ";
		 sql+=" where instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+"  ";
		 sql+=" )b on 1=1 group by diaName,b.totalcasecnt  order by CNT desc ";
		 List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		 return list;
	}
	/**
	 * 表格 16  一二级手术例数最多的前十名医师列表
	 */
	@Override
	public List<Map<String, Object>> findDocOperAList(Map<String, Object> map) {
		 String dateFrom = map == null?"":map.get("dateFrom").toString();
		 String dateTo = map == null?"":map.get("dateTo").toString();
		 String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		 String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		 String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuilder sql = new StringBuilder();
		sql.append(" select X.fopdc DOC, X.fdep DEP, X.fcnt CNT, X.fscnt FSCNT, X.fraio RAT  from ( \n");
		sql.append(" select a.opdc fopdc  ,a.P00300000 fdep,a.allcnt fcnt,a.sumcnt fscnt,ROUND(a.sumcnt/a.allcnt,4)*100 fraio,b.incnt fincnt, b.cmi fcmi from ( \n");
		sql.append(" SELECT op.PO0400 as opdc ,COUNT(op.PO0400) as allcnt ,mr.P00300000 , \n");
		sql.append(" SUM(CASE WHEN op.PO0301='1' OR op.PO0301='2' THEN 1 else 0 end) as sumcnt  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
		sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX  LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and op.PO0400 is not null  and op.PO1400='1' and  op.POTYPE='M' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY op.PO0400,mr.P00300000 \n");
		sql.append(" )a LEFT JOIN (SELECT   op.PO0400 as opdcb , \n");
		sql.append(" sum(case when pg.PARDRG_CODE is not null THEN 1 else 0 end ) as incnt , \n");
		sql.append(" ROUND(sum(ad.AS_WEIGHT)/COUNT(op.PO0400),2)  as cmi  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
	    sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX   \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg pg    on pg.PARID=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd ad     on ad.AS_DRG_CODE=pg.PARDRG_CODE \n");
	    sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and op.PO0400 is not null and pg.ver_region='"+groupScheme+"'  and op.PO1400='1' and  op.POTYPE='M'  and ad.AS_ID='"+stdId+"' \n");
	    sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
	    sql.append(" GROUP BY op.PO0400  )b on a.opdc=b.opdcb  ORDER BY a.sumcnt DESC )X where rownum<=10  \n");
	    List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 表格 17  三四级手术例数最多的前十名医师列表
	 */
	@Override
	public List<Map<String, Object>> findDocOperBList(Map<String, Object> map) {
		 String dateFrom = map == null?"":map.get("dateFrom").toString();
		 String dateTo = map == null?"":map.get("dateTo").toString();
		 String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		 String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		 String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuilder sql = new StringBuilder();
		sql.append(" select X.fopdc DOC, X.fdep DEP, X.fcnt CNT, X.fscnt FSCNT, X.fraio RAT  from ( \n");
		sql.append(" select a.opdc fopdc  ,a.P00300000 fdep,a.allcnt fcnt,a.sumcnt fscnt,ROUND(a.sumcnt/a.allcnt,4)*100 fraio,b.incnt fincnt, b.cmi fcmi from ( \n");
		sql.append(" SELECT op.PO0400 as opdc ,COUNT(op.PO0400) as allcnt ,mr.P00300000 , \n");
		sql.append(" SUM(CASE WHEN op.PO0301='3' OR op.PO0301='4' THEN 1 else 0 end) as sumcnt  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
		sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and op.PO0400 is not null  and op.PO1400='1' and  op.POTYPE='M' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY op.PO0400,mr.P00300000  \n");
		sql.append(" )a LEFT JOIN (SELECT   op.PO0400 as opdcb , \n");
		sql.append(" sum(case when pg.PARDRG_CODE is not null THEN 1 else 0 end ) as incnt , \n");
		sql.append(" ROUND(sum(ad.AS_WEIGHT)/COUNT(op.PO0400),2)  as cmi  \n");
		sql.append(" from drgs.MR_OPERATION  op  \n");
	    sql.append(" LEFT JOIN drgs.MR_IPINFO mr on op.PMAININDEX=mr.PMAININDEX LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg pg    on pg.PARID=mr.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_annualstd ad     on ad.AS_DRG_CODE=pg.PARDRG_CODE \n");
	    sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and op.PO0400 is not null and pg.ver_region='"+groupScheme+"'  and op.PO1400='1' and  op.POTYPE='M'  and ad.AS_ID='"+stdId+"' \n");
	    sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
	    sql.append(" GROUP BY op.PO0400  )b on a.opdc=b.opdcb  ORDER BY a.sumcnt DESC )X where rownum<=10  \n");
	    List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
   /**
    * 获取该重点临床专业的各项信息(第五章)
    */
	@Override
	public List<Map<String, Object>> findSpecCodeInformationList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+="";
		sql+="	select A.cnt CNT, B.pdcnt ACNT, D.pardrgcodecnt BCNT , C.acmi CMI, C.incnt CCNT , C.weig WEIGHT, C.TIM TIM , ";
		sql+="	C.FEE FEE ,C.LOWD LOWD ,C.LMD LMD, A.DAYS DAYS , A.FEES FEES,  ";
		sql+="	A.DRUGS , ROUND(decode( A.FEES,0,0,( A.DRUGS / A.FEES)),4)*100  DRUGAIO,  ";
		sql+="	A.MATERS ,ROUND(decode( A.FEES,0,0,( A.MATERS / A.FEES)),4)*100  MATERAIO ";
		sql+="	from  (select count( DISTINCT E.PMAININDEX) cnt , ";
		sql+="	ROUND(decode(count(E.PMAININDEX),0,0,SUM(E.P00320000)/count(E.PMAININDEX)),2) DAYS, "; 
		sql+="	ROUND(decode(count(E.PMAININDEX),0,0,SUM(E.P00800000)/count(E.PMAININDEX)),2) FEES,   ";
		sql+="	ROUND(decode(count(E.PMAININDEX),0,0,SUM(exp.DRUG_COST)/count(E.PMAININDEX)),2) DRUGS,  ";
		sql+="	ROUND(decode(count(E.PMAININDEX),0,0,SUM(exp.mater_cost)/count(E.PMAININDEX)),2) MATERS  ";
		sql+="	from DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX  left join drgs.MR_EXPENSE_SUM exp on E.PMAININDEX=exp.PMAININDEX ";
		sql+="  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  AND pg.ver_region='"+groupScheme+"' ";
		sql+="  left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+="  GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+="	LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code  ";
		sql+="  where instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dateFrom,dateTo,"E")+" AND cli.spec_code='"+specCode+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" ) A, ";
		sql+="  (select count(distinct dga.csd_drg_code ) pdcnt from drgs.t_clispecdrgs dga   ";
		sql+="  LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dga.csd_spec_code  where dga.csd_ver_code='"+drgVersion+"' and cli.spec_code='"+specCode+"' ) B, ";
		sql+="  (select count( DISTINCT E.PMAININDEX) incnt ,count( distinct  pg.Pardrg_Code) pgcnt  ,   ";
		sql+="  ROUND(decode(COUNT(pg.parid),0,0,SUM(T.AS_WEIGHT)/COUNT(pg.PARID)),2) acmi , ";
		sql+="  SUM(T.AS_WEIGHT) weig  , ";
		sql+="  round(decode(COUNT(pg.parid),0,0,sum(decode(T.AS_AVG_DAYS,0,0,E.P00320000/T.AS_AVG_DAYS))/COUNT(pg.parid)),2) TIM , ";
		sql+="  round(decode(COUNT(pg.parid),0,0,sum(decode(T.AS_AVG_FEE,0,0,E.P00800000/T.AS_AVG_FEE))/COUNT(pg.parid)),2) FEE,  "; 
		sql+="  ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='1'AND E.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 LOWD,  ";
		sql+="  ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='2'AND E.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 LMD  ";
		sql+="  from  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX  left join drgs.MR_EXPENSE_SUM exp on E.PMAININDEX=exp.PMAININDEX  ";
		sql+="  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  AND pg.ver_region='"+groupScheme+"' ";
		sql+="  left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+="  GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+="  LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code  ";
		sql+="  left join DRGS.T_ANNUALSTD T on  pg.PARDRG_CODE = T.AS_DRG_CODE ";
		sql+="  where instr('"+hospCode+"',BA.p00010001)>0 and pg.PARDRG_CODE IS NOT NULL  and T.AS_ID='"+stdId+"' "+Arith.mrAROutDateSql(dateFrom,dateTo,"E")+" and cli.spec_code='"+specCode+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" )C, ";
		sql+="  (select  count( distinct  pg.Pardrg_Code) pardrgcodecnt from  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX ";
		sql+="  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid   ";
		sql+="  where instr('"+hospCode+"',BA.p00010001)>0 AND pg.ver_region='"+groupScheme+"'  "+Arith.mrAROutDateSql(dateFrom,dateTo,"E")+"  ";
		sql+=" "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" ";
		sql+="	) D";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *获取该重点临床专业的出院病例数分布(第五章)
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeOutCntList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" select X.CODE CODE , X.DECC DECC , X.CNT CNT, X.WEIGHT WEIGHT FROM ";
		sql+=" (SELECT PG.PARDRG_CODE CODE , DS.DRG_DESC DECC, count(pg.parid) CNT , SUM(G.AS_WEIGHT) WEIGHT ";
		sql+=" from  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX  left join drgs.MR_EXPENSE_SUM exp on E.PMAININDEX=exp.PMAININDEX ";
		sql+=" left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  ";
		sql+=" LEFT JOIN DRGS.T_DRGS DS ON DS.DRG_CODE=PG.PARDRG_CODE  ";
		sql+=" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code RIGHT JOIN ( select distinct A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_WEIGHT, t.as_risk_level  FROM DRGS.T_PATIENTDRG A left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE  ";
		sql+=" left join  DRGS.MR_IPINFO B on A.PARID=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX  where instr('"+hospCode+"',BA.p00010001)>0 and T.AS_ID='"+stdId+"' AND A.PARDRG_CODE IS NOT NULL and  ";
		sql+=" B.P00320000 <61 "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" AND A.ver_region='"+groupScheme+"'  ";
		sql+=" ) G  ON E.PMAININDEX = G.PARID where instr('"+hospCode+"',BA.p00010001)>0 and cli.spec_code='"+specCode+"' and ds.ver_code='"+drgVersion+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" ";
		sql+=" GROUP BY PG.PARDRG_CODE, DS.DRG_DESC order by count(pg.parid) desc ) X WHERE rownum<=10  ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 5.3效率分析(第五章)
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeEfficiencyList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" select X.CODE CODE , X.DECC DECC , X.CNT CNT,X.DAYS DAYS, X.ASDAYS ASDAYS,X.FEES FEES, X.ASFEE ASFEE FROM ";
		sql+=" (SELECT PG.PARDRG_CODE CODE , DS.DRG_DESC DECC, count(pg.parid) CNT , ROUND(decode(count(E.PMAININDEX),0,0,SUM(E.P00320000)/count(E.PMAININDEX)),2) DAYS, ";
		sql+=" G.ad asdays ,ROUND(decode(count(E.PMAININDEX),0,0,SUM(E.P00800000)/count(E.PMAININDEX)),2) FEES, G.av asfee";
		sql+=" from  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX   ";
		sql+=" left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  ";
		sql+=" LEFT JOIN DRGS.T_DRGS DS ON DS.DRG_CODE=PG.PARDRG_CODE  ";
		sql+=" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code RIGHT JOIN ( select distinct A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_WEIGHT, t.as_risk_level,t.as_avg_days ad ,t.as_avg_fee av  FROM DRGS.T_PATIENTDRG A left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE  ";
		sql+=" left join  DRGS.MR_IPINFO B on A.PARID=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX  where instr('"+hospCode+"',BA.p00010001)>0 and T.AS_ID='"+stdId+"' AND A.PARDRG_CODE IS NOT NULL and  ";
		sql+=" B.P00320000 <61 "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" AND A.ver_region='"+groupScheme+"'  ";
		sql+=" ) G  ON E.PMAININDEX = G.PARID where instr('"+hospCode+"',BA.p00010001)>0 and cli.spec_code='"+specCode+"' and ds.ver_code='"+drgVersion+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" ";
		sql+=" GROUP BY PG.PARDRG_CODE, DS.DRG_DESC, G.ad ,G.av order by count(pg.parid) desc ) X WHERE rownum<=10  ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 5.4死亡病例分布(第五章)
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeDeadCntList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" SELECT A.CODE CODE, A.DECC DECC, A.LEVDESC LEVDESC, A.CNT CNT, A.DEADCNT DEADCNT FROM ( ";
		sql+=" SELECT PG.PARDRG_CODE CODE , DS.DRG_DESC DECC, decode(G.lev,'0','无','1','低','2','中低','3','中低','4','高','' ) LEVDESC, ";
		sql+=" count(pg.parid) CNT, sum(case when  E.P00620001='5' then 1 else 0 end ) DEADCNT ";
		sql+=" from  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX  ";
		sql+=" left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  ";
		sql+=" LEFT JOIN DRGS.T_DRGS DS ON DS.DRG_CODE=PG.PARDRG_CODE  ";
		sql+=" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code RIGHT JOIN ( select  distinct A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_WEIGHT, t.as_risk_level lev FROM DRGS.T_PATIENTDRG A left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE  ";
		sql+=" left join  DRGS.MR_IPINFO B on A.PARID=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX   where instr('"+hospCode+"',BA.p00010001)>0 and T.AS_ID='"+stdId+"' AND A.PARDRG_CODE IS NOT NULL and  ";
		sql+=" B.P00320000 <61 "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" AND A.ver_region='"+groupScheme+"' ";
		sql+=" ) G ON E.PMAININDEX = G.PARID where instr('"+hospCode+"',BA.p00010001)>0 and cli.spec_code='"+specCode+"' and ds.ver_code='"+drgVersion+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" ";
		sql+=" GROUP BY PG.PARDRG_CODE, DS.DRG_DESC, G.lev  order by DEADCNT  desc ";
		sql+=" ) A WHERE A.DEADCNT>0 ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 5.5病组缺失情况(第五章,但不需要加精细化条件)
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeDRGsLackList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" select dga.csd_drg_code CODE,dga.csd_drg_desc DECC,t.as_weight WEIGHT from drgs.t_clispecdrgs dga ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dga.csd_spec_code ";
		sql+=" left join drgs.T_ANNUALSTD t on dga.csd_drg_code=t.as_drg_code ";
		sql+=" where dga.csd_ver_code='"+drgVersion+"' and cli.spec_code='"+specCode+"' and t.AS_ID='"+stdId+"'  and dga.csd_drg_code not in ";
		sql+=" (SELECT PG.PARDRG_CODE CODE from  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid ";  
		sql+=" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code RIGHT JOIN ( select  distinct A.PARID,A.PARDRG_CODE ,T.AS_WEIGHT FROM DRGS.T_PATIENTDRG A left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE  ";
		sql+=" left join  DRGS.MR_IPINFO B on A.PARID=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX  where instr('"+hospCode+"',BA.p00010001)>0 and T.AS_ID='"+stdId+"' AND A.PARDRG_CODE IS NOT NULL and  ";
		sql+=" B.P00320000 <61 "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" AND A.ver_region='"+groupScheme+"' ";
		sql+=" ) G  ON E.PMAININDEX = G.PARID where instr('"+hospCode+"',BA.p00010001)>0 and cli.spec_code='"+specCode+"'  GROUP BY PG.PARDRG_CODE  )  GROUP BY dga.csd_drg_code,dga.csd_drg_desc,t.as_weight ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 5.6主要病种分析 (第五章)
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeMainADRGsList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" SELECT A.DECC DECC, A.CNT CNT ,A.acmi CMI ,A.DAYS DAYS, A.FEES FEES FROM ";
		sql+=" (select ats.adrg_desc DECC ,count(distinct E.PMAININDEX) CNT,ROUND(decode(COUNT(pg.parid),0,0,SUM(G.AS_WEIGHT)/COUNT(pg.PARID)),2) acmi, ";
		sql+=" ROUND(decode(count(E.PMAININDEX),0,0,SUM(E.P00320000)/count(E.PMAININDEX)),2) DAYS, ";
		sql+=" ROUND(decode(count(E.PMAININDEX),0,0,SUM(E.P00800000)/count(E.PMAININDEX)),2) FEES ";
		sql+=" from DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  ";
		sql+=" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+=" left join drgs.t_drgs ts on ts.drg_code=pg.pardrg_code and ts.ver_code='"+drgVersion+"' ";
		sql+=" left join drgs.t_adrg ats on ts.adrg_code=ats.adrg_code and ats.ver_code='"+drgVersion+"' ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = dd.parspec_code RIGHT JOIN ( select  distinct A.PARID,A.PARDRG_CODE,T.AS_AVG_FEE,T.AS_AVG_DAYS ,T.AS_WEIGHT, t.as_risk_level FROM DRGS.T_PATIENTDRG A left join DRGS.T_ANNUALSTD T on A.PARDRG_CODE = T.AS_DRG_CODE  ";
		sql+=" left join  DRGS.MR_IPINFO B on A.PARID=b.pmainindex LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX   where instr('"+hospCode+"',BA.p00010001)>0 and T.AS_ID='"+stdId+"' AND A.PARDRG_CODE IS NOT NULL and  ";
		sql+=" B.P00320000 <61 "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" AND A.ver_region='"+groupScheme+"'  ";
		sql+=" ) G  ON E.PMAININDEX = G.PARID where instr('"+hospCode+"',BA.p00010001)>0 and cli.spec_code='"+specCode+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+"  group by ats.adrg_desc order by count(distinct E.PMAININDEX) desc )A where rownum<=10 ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	
	/**
	 * 附表1:全院医疗服务综合能力情况表
	 */
	@Override
	public List<Map<String, Object>> findHospAbilityList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT COUNT(p.PARID) CNT ,ROUND(decode(COUNT(P.PARID),0,0,SUM(A .AS_WEIGHT) / COUNT(P.PARID)), 2 )  CMI, \n");
		sql.append(" COUNT(DISTINCT P.PARDRG_CODE) DRGS ,ROUND(SUM(A.AS_WEIGHT),2) WEIGHT,  round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,M.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  TIME, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,M.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2)  FEE , \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 ARATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 BRATIO \n");
		sql.append(" FROM DRGS.MR_IPINFO M LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=M.PMAININDEX  \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M .PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and A.AS_ID='"+stdId+"'  AND A.AS_WEIGHT IS not NULL  and p.ver_region='"+groupScheme+"' and P.PARDRG_CODE is not null \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"M")+" \n");
		sql.append(" ORDER BY CNT DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *附表1:全院各科室医疗服务综合能力情况表
	 */
	@Override
	public List<Map<String, Object>> findAllDepAbilityList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT dt.dep_name DECC,COUNT(p.PARID) CNT,ROUND(decode(COUNT(P.PARID),0,0,SUM(A .AS_WEIGHT) / COUNT(P.PARID)), 2 ) CMI, \n");
		sql.append(" COUNT(DISTINCT P.PARDRG_CODE) DRGS ,ROUND(SUM(A.AS_WEIGHT),2) WEIGHT, \n");
	    sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,M.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2) TIME, \n"); 
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,M.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) FEE, \n"); 
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 ARATIO, \n"); 
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND M.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 BRATIO \n");
		sql.append(" FROM DRGS.MR_IPINFO M LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=M.PMAININDEX  left join drgs.t_department dt on dt.dep_code=m.P00300001 \n");
		sql.append(" LEFT JOIN DRGS.T_PATIENTDRG P ON M.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0 and A.AS_ID='"+stdId+"'  AND A.AS_WEIGHT  IS not NULL  and p.ver_region='"+groupScheme+"' and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"M")+" \n");
		sql.append(" GROUP BY dt.dep_name ORDER BY CNT DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表2 全院 DRG组数和覆盖率
	 */
	@Override
	public List<Map<String, Object>> findHospallDRGsList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT Y.drgTotalCount CNT,ROUND(decode(Y.drgTotalCount,0,0,X.drgsCount/Y.drgTotalCount),4)*100 RADIO FROM \n");
		sql.append(" (SELECT COUNT(DISTINCT A.PARDRG_CODE) drgsCount \n");    
		sql.append(" FROM DRGS.T_PATIENTDRG A,DRGS.MR_BASEINFO  BA , DRGS.MR_IPINFO B \n");
		sql.append(" WHERE BA.PMAININDEX=B.PMAININDEX and instr('"+hospCode+"', BA.p00010001)>0 and A.ver_region='"+groupScheme+"'  AND A.PARDRG_CODE is NOT NULL  AND  A.PARID = B.PMAININDEX  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" ) X , \n");
		sql.append(" (select COUNT( ds.DRG_CODE ) drgTotalCount from drgs.t_drgs ds where ds.VER_CODE= '"+drgVersion+"' ) Y \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表2 各MDC专业DRG病组覆盖情况及医疗服务综合能力情况
	 */
	@Override
	public List<Map<String, Object>> findAllmdcDRGsList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT  X.MDC CODE ,X.mdc_desc DECC,X.mdcincnt ACNT ,Y.drgTotalCount BCNT,ROUND(decode(Y.drgTotalCount,0,0,X.drgsCount/Y.drgTotalCount),4)*100 BRATIO,X.mdcmi CMI,X.drgsCount CCNT, X.weight WEIGHT, \n");
		sql.append(" X.mdctime TIME ,X.mdcfee FEE,X.mdcratio LRT ,X.mdcrate MLLRT FROM \n");
		sql.append(" ( SELECT A.MDC, m.mdc_desc,count(A.PARID) mdcincnt, decode(count(A.PARID),0,0,sum(T.AS_WEIGHT)/count(A.PARID)) mdcmi, \n");
		sql.append(" sum(T.AS_WEIGHT) weight ,COUNT(DISTINCT A.PARDRG_CODE) drgsCount , \n");  
		sql.append(" round(decode(COUNT(A.PARID),0,0,SUM(decode(T.AS_AVG_DAYS,0,0,B.P00320000/T.AS_AVG_DAYS))/COUNT(A.PARID)),2) AS mdctime, \n");
		sql.append(" round(decode(COUNT(A.PARID),0,0,SUM(decode(T.AS_AVG_FEE,0,0,B.P00800000/T.AS_AVG_FEE))/COUNT(A.PARID)),2) as mdcfee , \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='1'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 as mdcratio, \n");
		sql.append(" ROUND(decode(sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN T.AS_RISK_LEVEL='2'AND B.P00620001='5' then 1 else 0 end)/sum(case WHEN T.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 as mdcrate \n");
		sql.append(" FROM DRGS.T_PATIENTDRG A, DRGS.T_ANNUALSTD T, DRGs.T_MDCGROUP m  , DRGS.MR_BASEINFO  BA ,DRGS.MR_IPINFO B \n");
		sql.append(" WHERE BA.PMAININDEX=B.PMAININDEX and instr('"+hospCode+"', BA.p00010001)>0 and A.ver_region='"+groupScheme+"' and A.PARDRG_CODE = T.AS_DRG_CODE  AND T.AS_ID='"+stdId+"'  \n");
		sql.append(" and a.ver_code = m.ver_code AND A.PARDRG_CODE is NOT NULL and t.as_mdc_code = m.mdc_code   AND  A.PARID = B.PMAININDEX AND B.P00320000 <61  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"B")+" \n");
		sql.append(" group by A.MDC, m.mdc_desc ) X LEFT JOIN \n");
		sql.append(" (select mg.mdc_code, count(DISTINCT drg.drg_code) drgTotalCount,mg.ver_code,mg.mdc_desc \n");
		sql.append(" from DRGs.t_Drgs drg, DRGs.t_Adrg adrg, DRGs.t_Mdcgroup mg  \n");
		sql.append(" WHERE drg.adrg_code = adrg.adrg_code \n");
		sql.append(" and adrg.mdc_code = mg.mdc_code \n");
		sql.append(" and adrg.VER_CODE=mg.ver_code and mg.ver_code=drg.ver_code and drg.ver_code='"+drgVersion+"' \n");
		sql.append(" group by mg.mdc_code,mg.ver_code,mg.mdc_desc )Y  ON X.MDC=Y.mdc_code \n");
		sql.append(" ORDER BY X.mdcincnt DESC \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表3  住院病例DRG总权重最高的二十位主治医师
	 */
	@Override
	public List<Map<String, Object>> findDocByWeightList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql=new StringBuffer();
		sql.append(" select  DISTINCT mr.P00600300 DECC ,dt.dep_name DEP,SUM(A.AS_WEIGHT) WEIGHT,COUNT(DISTINCT p.PARDRG_CODE) DCNT,ROUND(decode(COUNT(P.PARID),0,0,SUM(A.AS_WEIGHT)/COUNT(P.PARID)),2) CMI , \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,mr.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  TIME, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,mr.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) FEE, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 LRATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 MRATIO, \n");
		sql.append(" count(p.PARID) INCNT \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append(" left join drgs.t_department dt on dt.dep_code=mr.P00300001 LEFT JOIN DRGS.T_PATIENTDRG P ON mr.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and mr.P00600300 IS NOT NULL AND A.AS_ID='"+stdId+"' AND A.AS_WEIGHT IS not NULL  and p.ver_region='"+groupScheme+"' and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY mr.P00600300,dt.dep_name  ORDER BY weight DESC  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 附表4 CMI值最高的二十位主治医师
	 */
	@Override
	public List<Map<String, Object>> findDocByCMIList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql=new StringBuffer();
		sql.append(" select X.physician DECC,X.depname DEP, X.weight WEIGHT,X.DCNT DCNT, X.cmi CMI,X.time TIME, X.fee FEE, X.LRATIO LRATIO, X.MRATIO MRATIO, X.INCNT INCNT \n");
		sql.append(" from (select DISTINCT mr.P00600300 physician  ,dt.dep_name depname ,SUM(A.AS_WEIGHT) weight,COUNT(DISTINCT p.PARDRG_CODE) DCNT, ROUND(decode(COUNT(P.PARID),0,0,SUM(A.AS_WEIGHT)/COUNT(P.PARID)),2) CMI , \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_DAYS,0,0,mr.P00320000/A.AS_AVG_DAYS))/COUNT(P.PARID)),2)  TIME, \n");
		sql.append(" round(decode(COUNT(P.PARID),0,0,SUM(decode(A.AS_AVG_FEE,0,0,mr.P00800000/A.AS_AVG_FEE))/COUNT(P.PARID)),2) FEE , \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='1'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='1' then 1 else 0 end)),4)*100 LRATIO, \n");
		sql.append(" ROUND(decode(sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end),0,0,sum(case WHEN A.AS_RISK_LEVEL='2'AND mr.P00620001='5' then 1 else 0 end)/sum(case WHEN A.AS_RISK_LEVEL='2' then 1 else 0 end)),4)*100 MRATIO, \n");
		sql.append(" count(p.PARID) INCNT \n");
		sql.append(" from drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  \n");
		sql.append("  left join drgs.t_department dt on dt.dep_code=mr.P00300001 LEFT JOIN DRGS.T_PATIENTDRG P ON mr.PMAININDEX = P.PARID \n");
		sql.append(" LEFT JOIN DRGS.T_ANNUALSTD A  ON P.PARDRG_CODE = A.AS_DRG_CODE \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and mr.P00600300 IS NOT NULL AND A.AS_ID='"+stdId+"' AND A.AS_WEIGHT IS not NULL  and p.ver_region='"+groupScheme+"'  and P.PARDRG_CODE is not null  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" \n");
		sql.append(" GROUP BY mr.P00600300,dt.dep_name ORDER BY CMI DESC  ) X \n");
		sql.append(" where X.incnt>=10 \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *附表5 未入组病例覆盖的所有主要诊断
	 */
	@Override
	public List<Map<String, Object>> findOldNotInGroupDiagList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT X.PD0101 CODE, X.PD0100 DECC, X.cnt CNT,ROUND(decode(Y.allcnt,0,0,X.cnt/Y.allcnt*100),2) RATIO \n");
		sql.append(" from (select de.PD0101, de.PD0100 ,count(1) cnt from drgs.MR_IPINFO m left join drgs.MR_DIAGNOSE de on m.PMAININDEX=de.PMAININDEX \n");
		sql.append(" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and de.pdtype='M' AND  m.P00320000<61 and p.PARDRG_CODE is null and p.VER_CODE= '"+drgVersion+"' \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" \n");
		sql.append(" GROUP BY de.PD0101, de.PD0100 ORDER BY cnt DESC ) X, \n");
		sql.append(" (select count(1) allcnt from drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX  \n");
		sql.append(" LEFT JOIN drgs.t_patientdrg p on m.PMAININDEX=p.PARID \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 and m.P00320000<61 and p.PARDRG_CODE is null  \n");
		sql.append(" "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" \n");
		sql.append(" and p.ver_region='"+groupScheme+"' )Y \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *第六章 未入组病例
	 */
	@Override
	public List<Map<String, Object>> findNoInGroupDataList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" select A.acnt ACNT,A.acnt-B.nocnt CCNT ,B.nocnt BCNT,ROUND(decode(B.allcnt-B.overs,0,0,(B.incnt)*100/(B.allcnt-B.overs)),2) GROUPRATIO FROM ";
		sql+=" (SELECT count(distinct rs.pmainindex) acnt FROM drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX LEFT JOIN drgs.Mr_Redisgroup rs on m.PMAININDEX=rs.pmainindex ";
		sql+=" WHERE instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" and rs.ver_code='"+drgVersion+"' ) A, ";
		sql+=" (SELECT sum(CASE WHEN m.P00320000 <61 AND pg.PARDRG_CODE is  NULL   THEN 1 else 0 END ) nocnt  , ";
		sql+=" sum(CASE WHEN m.P00320000 <61 AND pg.PARDRG_CODE is not NULL then 1 else 0 end ) incnt, ";
		sql+=" COUNT(m.pmainindex) allcnt, SUM(CASE WHEN m.P00320000 > 60 THEN 1 else 0 end  ) overs ";
		sql+=" FROM drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX  LEFT JOIN drgs.t_Patientdrg pg on m.PMAININDEX=pg.parid ";
		sql+=" WHERE instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" and pg.ver_region='"+groupScheme+"' ) B ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *表格 25住院天数大于60天病例科室分布表 
	 */
	@Override
	public List<Map<String, Object>> findNoInGroupDepOverSixtyList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String sql="";
		sql+=" select count(distinct m.pmainindex) CNT , m.P00300000 DEP from drgs.MR_IPINFO m LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX  ";
		sql+=" where instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" and  m.P00320000 >60  ";
		sql+="group by m.P00300000 order by count(distinct m.pmainindex) desc ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 表格 26 未入组病例科室分布表
	 */
	@Override
	public List<Map<String, Object>> findNoInGroupDepList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String sql="";
		sql+=" select count(distinct m.pmainindex) CNT , m.P00300000 DEP from drgs.MR_IPINFO m  ";
		sql+=" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=m.PMAININDEX LEFT JOIN drgs.t_Patientdrg pg on m.PMAININDEX=pg.parid ";
		sql+=" where instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"m")+" and  m.P00320000 <61 and pg.pardrg_code is null  ";
		sql+=" group by m.P00300000 order by count(distinct m.pmainindex) desc ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 *6.2未入组原因分布
	 */
	@Override
	public List<Map<String, Object>> findNotInGroupReasonList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" SELECT g.NOTIN_REASON REASON ,d.dic_desc DECC, d.dic_resume RESUME , COUNT(G.MR_ADM) CNT FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX LEFT JOIN drgs.t_groupinglog g ON mr.PMAININDEX=g.MR_ADM ";
		sql+=" LEFT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID ";
		sql+=" left join drgs.mr_dictionarydata d on d.dic_value=g.notin_reason and d.dic_code='NOTIN_REASON' ";
		sql+=" where instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" and  mr.P00320000 <61 and p.pardrg_code is null ";
		sql+=" and p.ver_region='"+groupScheme+"' AND  g.NOTIN_REASON not in ('0') GROUP BY g.NOTIN_REASON ,d.dic_desc, d.dic_resume order by COUNT(G.MR_ADM) desc ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	
	/**
	 *图表14——重点专业例数最多的十种手术
	 */
	@Override
	public List<Map<String, Object>> findSpecTopTenOper(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String sql="";
		sql+=" SELECT * FROM (SELECT oper.po0100 operName,count(*) as operCnt FROM DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO BA ON BA.PMAININDEX=A.PMAININDEX  ";
		sql+="  left join drgs.t_patientdrg pg on A.pmainindex=pg.parid  AND pg.ver_region='"+groupScheme+"' ";
		sql+=" LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001  ";
		sql+=" left join drgs.t_clispec spec on spec.spec_code =dd.parspec_code ";
		sql+=" left join drgs.mr_operation oper on oper.pmainindex=a.pmainindex and oper.potype='M' ";
		sql+=" WHERE instr('"+hospCode+"',BA.p00010001)>0 and spec.spec_code='"+specCode+"' and oper.po0100 is not null "+Arith.mrAROutDateSql(dateFrom,dateTo,"a")+" "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" ";
		sql+=" group by oper.po0100 order by opercnt desc) WHERE ROWNUM<11 ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 表格 9  各临床专业不同权重区间病例分布表
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeWeightIntervalList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String isRefine = map == null?"":map.get("isRefine").toString(); //是否精细化 1是精细
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		
		StringBuilder sql = new StringBuilder();
		
		sql.append(" select a.*,round(a.incasecnt/totalcasecnt*100,2) RATE from  \n");
		sql.append(" (SELECT spec.spec_desc ,case when AS_WEIGHT> 0 and AS_WEIGHT<=0.5 then '区间1'  when AS_WEIGHT> 0.5 and AS_WEIGHT<=0.8 then '区间2' \n");
	    sql.append("  when AS_WEIGHT> 0.8 and AS_WEIGHT<=1.2 then '区间3'when AS_WEIGHT> 1.2 and AS_WEIGHT<=2 then '区间4' \n");
		sql.append("  when AS_WEIGHT> 2 and AS_WEIGHT<=5 then '区间5' when AS_WEIGHT>5 then '区间6' end weight_level, \n");
		sql.append("  COUNT(DISTINCT p.PARDRG_CODE) DRGSCNT,count(*) INCASECNT \n");
		sql.append("  FROM DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  LEFT JOIN  drgs.t_patientdrg p ON a.PMAININDEX=p.PARID \n");
		sql.append("  LEFT JOIN  drgs.t_annualstd std ON p.PARDRG_CODE=std.AS_DRG_CODE  \n");
		sql.append("  LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"'  \n");
		sql.append("  GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001  \n");
		sql.append("  left join drgs.t_clispec spec on spec.spec_code =dd.parspec_code \n");
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not null AND std.AS_ID='"+stdId+"' and a.P00320000 <61   \n");
		sql.append("  and std.as_weight is not null "+Arith.mrAROutDateSql(dateFrom,dateTo,"A")+"  and spec.spec_code is not null  \n");
		if("1".equals(isRefine)){
			sql.append(" "+Arith.pdInDpDRGSql(drgVersion,"all","p")+" \n");
			}
		sql.append("  GROUP BY spec.spec_desc, case when AS_WEIGHT> 0 and AS_WEIGHT<=0.5 then '区间1' when AS_WEIGHT> 0.5 and AS_WEIGHT<=0.8 then '区间2' \n");
		sql.append("  when AS_WEIGHT> 0.8 and AS_WEIGHT<=1.2 then '区间3'when AS_WEIGHT> 1.2 and AS_WEIGHT<=2 then '区间4' \n");
		sql.append("  when AS_WEIGHT> 2 and AS_WEIGHT<=5 then '区间5' when AS_WEIGHT>5 then '区间6' end \n");
		sql.append("  order by spec.spec_desc,weight_level desc)a left join ( select spec_desc,count(*) totalcasecnt FROM DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX   \n");
		sql.append("  LEFT JOIN  drgs.t_patientdrg p ON a.PMAININDEX=p.PARID LEFT JOIN  drgs.t_annualstd std ON p.PARDRG_CODE=std.AS_DRG_CODE  \n");
		sql.append("  LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"'  \n");
		sql.append("  GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001  \n");
		sql.append("  left join drgs.t_clispec spec on spec.spec_code =dd.parspec_code \n");
		sql.append("  WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not null AND std.AS_ID='"+stdId+"' \n");
		sql.append("  and a.P00320000 <61  and std.as_weight is not null  \n");
		sql.append("  "+Arith.mrAROutDateSql(dateFrom,dateTo,"A")+" and spec.spec_code is not null \n");
		if("1".equals(isRefine)){
			sql.append(" "+Arith.pdInDpDRGSql(drgVersion,"all","p")+" \n");
			}
		sql.append("  group by spec.spec_desc)b on a.spec_desc=b.spec_desc \n");
		  
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 表格 9 各临床专业
	 */
	@Override
	public List<Map<String, Object>> findSpecDescEveryList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		StringBuilder sql = new StringBuilder();
		
		sql.append(" select distinct spec.spec_desc DECC \n");
		sql.append(" from  drgs.MR_IPINFO  A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t   \n");
		sql.append(" where t.version='"+drgVersion+"' GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001  \n");
		sql.append(" left join drgs.t_clispec spec on spec.spec_code =dd.parspec_code  \n");
		sql.append(" where instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"A")+"  and spec.spec_desc is not null \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 5.7 各级手术占比情况
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeOperLevelCntList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" SELECT  B.PO0301 LEVE , count(distinct E.PMAININDEX) CNT ";
		sql+=" FROM  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX ";
		sql+="  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  AND pg.ver_region='"+groupScheme+"' ";
		sql+=" left join drgs.mr_operation b on b.pmainindex=E.PMAININDEX ";
		sql+=" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = DD.parspec_code ";
		sql+=" where instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"E")+" AND cli.spec_code='"+specCode+"' ";
		sql+=" and b.PO1400='1' and b.POTYPE='M'  "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" group by B.PO0301 ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 表格 24 主刀三四级手术例数最多的十名术者
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeOperDocCntList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String sql="";
		sql+=" SELECT b.PO0400 DOC , count(1) CNT , ";
		sql+=" sum(case when B.PO0301='1' then 1 else 0 end ) ALEVEL, sum(case when B.PO0301='2' then 1 else 0 end ) BLEVEL, ";
		sql+=" sum(case when B.PO0301='3' then 1 else 0 end ) CLEVEL, sum(case when B.PO0301='4' then 1 else 0 end ) DLEVE, ";
		sql+=" sum(case when B.PO0301='3' OR B.PO0301='4'  then 1 else 0 end ) ELEVE, ";
		sql+=" DECODE(count(1),0,0, round( sum(case when B.PO0301='3' OR B.PO0301='4' then 1 else 0 end )*100/count(1),'2') )  RATI ";
		sql+=" FROM  DRGS.MR_IPINFO E LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=E.PMAININDEX  left join drgs.mr_operation b on b.pmainindex=E.PMAININDEX ";
		sql+="  left join drgs.t_patientdrg pg on E.pmainindex=pg.parid  AND pg.ver_region='"+groupScheme+"' ";
		sql+=" left join (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t where t.version='"+drgVersion+"' ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=E.P00300001  ";
		sql+=" LEFT JOIN DRGS.T_CLISPEC CLI ON CLI.SPEC_CODE = DD.parspec_code  ";
		sql+=" where  instr('"+hospCode+"',BA.p00010001)>0 "+Arith.mrAROutDateSql(dateFrom,dateTo,"E")+" AND cli.spec_code='"+specCode+"' ";
		sql+=" and b.PO1400='1' and b.POTYPE='M' and b.PO0400 is not null "+Arith.pdInDpDRGSql(drgVersion,specCode,"pg")+" group by b.PO0400  ";
		sql+=" order by sum(case when B.PO0301='3' OR B.PO0301='4'  then 1 else 0 end ) desc ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	/**
	 * 5.2专业内病例RW分布 （第五章）
	 */
	@Override
	public List<Map<String, Object>> findSpecCodeRWDistributeList(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString(); //获得院区信息
		String stdId = map== null?"":map.get("stdId").toString(); //获取标杆表中的唯一识别码
		String groupScheme = map== null?"":map.get("groupScheme").toString(); //获取分组方案
		String specCode = map == null?"":map.get("specCode").toString().trim();
		String drgVersion = map == null?"":map.get("drgVersion").toString();
		String sql="";
		sql+=" select a.*,round(a.incasecnt/totalcasecnt*100,2) RATIO from ( ";
		sql+=" SELECT (case when AS_WEIGHT> 0 and AS_WEIGHT<=0.5 then '区间1'  when AS_WEIGHT> 0.5 and AS_WEIGHT<=0.8 then '区间2' ";
		sql+=" when AS_WEIGHT> 0.8 and AS_WEIGHT<=1.2 then '区间3'when AS_WEIGHT> 1.2 and AS_WEIGHT<=2 then '区间4' ";
		sql+=" when AS_WEIGHT> 2 and AS_WEIGHT<=5 then '区间5' when AS_WEIGHT>5 then '区间6' end) weight_level, ";
		sql+=" COUNT(DISTINCT p.PARDRG_CODE) DRGSCNT,count(1) INCASECNT,ROUND(AVG(A.P00320000),2) AVGDAY, ";
	    sql+=" ROUND(AVG(P00800000),2) AVGFEE FROM DRGS.MR_IPINFO A LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX  LEFT JOIN  drgs.t_patientdrg p ON a.PMAININDEX=p.PARID ";
		sql+=" LEFT JOIN  drgs.t_annualstd std ON p.PARDRG_CODE=std.AS_DRG_CODE  ";
		sql+=" LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t  ";
	    sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001  ";
		sql+=" left join drgs.t_clispec spec on spec.spec_code =dd.parspec_code ";
		sql+=" WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not null AND std.AS_ID='"+stdId+"' and a.P00320000 <61  and std.as_weight is not null  ";
		sql+=" "+Arith.mrAROutDateSql(dateFrom,dateTo,"A")+"   ";
	    sql+=" and spec.spec_code='"+specCode+"'  "+Arith.pdInDpDRGSql(drgVersion,specCode,"p")+" GROUP BY  case when AS_WEIGHT> 0 and AS_WEIGHT<=0.5 then '区间1' when AS_WEIGHT> 0.5 and AS_WEIGHT<=0.8 then '区间2' ";
		sql+=" when AS_WEIGHT> 0.8 and AS_WEIGHT<=1.2 then '区间3'when AS_WEIGHT> 1.2 and AS_WEIGHT<=2 then '区间4' ";
		sql+=" when AS_WEIGHT> 2 and AS_WEIGHT<=5 then '区间5' when AS_WEIGHT>5 then '区间6' end ";
		sql+=" order by weight_level )a left join ( select count(*) totalcasecnt FROM DRGS.MR_IPINFO A ";
		sql+=" LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=A.PMAININDEX LEFT JOIN  drgs.t_patientdrg p ON a.PMAININDEX=p.PARID ";
		sql+=" LEFT JOIN  drgs.t_annualstd std ON p.PARDRG_CODE=std.AS_DRG_CODE ";
		sql+=" LEFT JOIN (SELECT T.PARDEP_CODE,T.PARSPEC_CODE FROM DRGS.T_DEPARTMENTDRG t  ";
		sql+=" GROUP BY T.PARDEP_CODE,T.PARSPEC_CODE) DD ON DD.PARDEP_CODE=A.P00300001  ";
		sql+=" left join drgs.t_clispec spec on spec.spec_code =dd.parspec_code ";
		sql+=" WHERE instr('"+hospCode+"',BA.p00010001)>0 and p.ver_region='"+groupScheme+"' and p.PARDRG_CODE is not null AND std.AS_ID='"+stdId+"' and a.P00320000 <61  and std.as_weight is not null  ";
		sql+=" "+Arith.mrAROutDateSql(dateFrom,dateTo,"A")+" ";
		sql+=" and spec.spec_code='"+specCode+"' "+Arith.pdInDpDRGSql(drgVersion,specCode,"p")+" )b on 1=1 ";
	    List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
 
}
